package com.ruoyi.smtdashboard.mapper;

import com.ruoyi.smtdashboard.domain.entity.Charts;
import com.ruoyi.smtdashboard.domain.entity.Smtdashboard;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface SmtDashboardMapper {

    @Select("<script>" +
            "select line,sum(claim_total) as sum1,sum(claim_h1) as sum2,sum(claim_c) as sum3 from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY line order by CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryClaim(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

    @Select("<script>" +
            "select month,line,sum(claim_total) as sum1,sum(claim_h1) as sum2,sum(claim_c) as sum3 from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY month,line order by STR_TO_DATE(month, '%Y-%b'),CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryClaimMonthList(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);


    @Results(id = "qualityResultMap", value = {
            @Result(property = "id", column = "id"),
            @Result(property = "year", column = "year"),
            @Result(property = "month", column = "month"),
            @Result(property = "line", column = "line"),

            // Claim 相关字段
            @Result(property = "claimTotal", column = "claim_total"),
            @Result(property = "claimH1", column = "claim_h1"),
            @Result(property = "claimC", column = "claim_c"),

            // Sorting Fee 字段
            @Result(property = "sortingFeeTotal", column = "sorting_fee_total"),
            @Result(property = "sortingFeeIqc", column = "sorting_fee_iqc"),
            @Result(property = "sortingFeeClaim", column = "sorting_fee_claim"),

            // 生产指标
            @Result(property = "inputAmount", column = "input_amount"),
            @Result(property = "scarpAmount", column = "scarp_amount"),
            @Result(property = "scarpRate", column = "scarp_rate"),
            @Result(property = "nonConformity", column = "non_conformity"),
            @Result(property = "capa", column = "capa"),

            // OQC 检验数据
            @Result(property = "oqcScanQty", column = "oqc_scan_qty"),
            @Result(property = "oqcScanNgQty", column = "oqc_scan_ng_qty"),
            @Result(property = "oqcScanNg", column = "oqc_scan_ng"),
            @Result(property = "oqcInspLot", column = "oqc_insp_lot"),
            @Result(property = "oqcRejectLot", column = "oqc_reject_lot"),
            @Result(property = "oqcLrr", column = "oqc_lrr"),
            @Result(property = "oqcInspQty", column = "oqc_insp_qty"),
            @Result(property = "oqcNgQty", column = "oqc_ng_qty"),
            @Result(property = "oqcDppm", column = "oqc_dppm"),

            // OBA 检验数据
            @Result(property = "obaTimes", column = "oba_times"),
            @Result(property = "obaRejectTime", column = "oba_reject_time"),
            @Result(property = "obaNg", column = "oba_ng"),

            // IPQC 检验数据
            @Result(property = "ipqcFindings", column = "ipqc_findings"),
            @Result(property = "ipqcMajor", column = "ipqc_major"),
            @Result(property = "ipqcMinor", column = "ipqc_minor"),
            @Result(property = "ipqcCloseMil", column = "ipqc_close_mil"),
            @Result(property = "ipqcCloseRate", column = "ipqc_close_rate"),

            // 第三方检验数据
            @Result(property = "partyFindings3Rd", column = "party_findings_3rd"),
            @Result(property = "partyMajor3Rd", column = "party_major_3rd"),
            @Result(property = "partyMinor3Rd", column = "party_minor_3rd"),
            @Result(property = "partyCloseMil3Rd", column = "party_close_mil_3rd"),
            @Result(property = "partyCloseRate3Rd", column = "party_close_rate_3rd"),

            // 合规性检查
            @Result(property = "complianceTotal", column = "compliance_total"),
            @Result(property = "compCustomer", column = "comp_customer"),
            @Result(property = "compInternal", column = "comp_internal"),

            // FOX 指标
            @Result(property = "foxTotal", column = "fox_total"),
            @Result(property = "foxIct", column = "fox_ict"),
            @Result(property = "foxSpi", column = "fox_spi"),
            @Result(property = "foxFpy", column = "fox_fpy"),
            @Result(property = "foxDelay", column = "fox_delay"),

            // SPI 测试
            @Result(property = "spiInput", column = "spi_input"),
            @Result(property = "spiOutput", column = "spi_output"),
            @Result(property = "spiFpy", column = "spi_fpy"),

            // AOI 测试
            @Result(property = "aoiInput", column = "aoi_input"),
            @Result(property = "aoiFpQty", column = "aoi_fp_qty"),
            @Result(property = "aoiOutput", column = "aoi_output"),
            @Result(property = "aoiFpy", column = "aoi_fpy"),
            @Result(property = "aoiFy", column = "aoi_fy"),

            // ICT 测试
            @Result(property = "ictInput", column = "ict_input"),
            @Result(property = "ictFpQty", column = "ict_fp_qty"),
            @Result(property = "ictOutput", column = "ict_output"),
            @Result(property = "ictFpy", column = "ict_fpy"),
            @Result(property = "ictFy", column = "ict_fy"),

            //追加数据IPQC+第三方
            @Result(property = "ipqcLatestCloseMil", column = "ipqc_latest_close_mil"),
            @Result(property = "partyLatestCloseMil", column = "party_latest_close_mil")
    })
    @Select("<script>" +
            "SELECT \n" +
            "    line,\n" +
            "    SUM(claim_total) AS claim_total,\n" +
            "    SUM(claim_h1) AS claim_h1,\n" +
            "    SUM(claim_c) AS claim_c,\n" +
            "    SUM(sorting_fee_total) AS sorting_fee_total,\n" +
            "    SUM(sorting_fee_iqc) AS sorting_fee_iqc,\n" +
            "    SUM(sorting_fee_claim) AS sorting_fee_claim,\n" +
            "    SUM(input_amount) AS input_amount,\n" +
            "    SUM(scarp_amount) AS scarp_amount,\n" +
            "    AVG(scarp_rate) AS scarp_rate,  -- 注意：比率可能需要特殊处理\n" +
            "    SUM(non_conformity) AS non_conformity,\n" +
            "    SUM(capa) AS capa,\n" +
            "    SUM(oqc_scan_qty) AS oqc_scan_qty,\n" +
            "    SUM(oqc_scan_ng_qty) AS oqc_scan_ng_qty,\n" +
            "    AVG(oqc_scan_ng) AS oqc_scan_ng,  -- 注意：比率可能需要特殊处理\n" +
            "    SUM(oqc_insp_lot) AS oqc_insp_lot,\n" +
            "    SUM(oqc_reject_lot) AS oqc_reject_lot,\n" +
            "    SUM(oqc_reject_lot)/sum(oqc_insp_lot)*100 AS oqc_lrr,  -- 注意：比率可能需要特殊处理\n" +
            "    SUM(oqc_insp_qty) AS oqc_insp_qty,\n" +
            "    SUM(oqc_ng_qty) AS oqc_ng_qty,\n" +
            "    sum(oqc_ng_qty)/sum(oqc_insp_qty)*1000000 AS oqc_dppm,  -- 注意：比率可能需要特殊处理\n" +
            "    SUM(oba_times) AS oba_times,\n" +
            "    SUM(oba_reject_time) AS oba_reject_time,\n" +
            "    AVG(oba_ng) AS oba_ng,  -- 注意：比率可能需要特殊处理\n" +
            "    SUM(ipqc_findings) AS ipqc_findings,\n" +
            "    SUM(ipqc_major) AS ipqc_major,\n" +
            "    SUM(ipqc_minor) AS ipqc_minor,\n" +
            "    SUM(ipqc_close_mil) AS ipqc_close_mil,\n" +
            "    AVG(ipqc_close_rate) AS ipqc_close_rate,  -- 注意：比率可能需要特殊处理\n" +
            "    SUM(party_findings_3rd) AS party_findings_3rd,\n" +
            "    SUM(party_major_3rd) AS party_major_3rd,\n" +
            "    SUM(party_minor_3rd) AS party_minor_3rd,\n" +
            "    SUM(party_close_mil_3rd) AS party_close_mil_3rd,\n" +
            "    AVG(party_close_rate_3rd) AS party_close_rate_3rd,  -- 注意：比率可能需要特殊处理\n" +
            "    SUM(compliance_total) AS compliance_total,\n" +
            "    SUM(comp_customer) AS comp_customer,\n" +
            "    SUM(comp_internal) AS comp_internal,\n" +
            "    SUM(fox_total) AS fox_total,\n" +
            "    SUM(fox_ict) AS fox_ict,\n" +
            "    SUM(fox_spi) AS fox_spi,\n" +
            "    SUM(fox_fpy) AS fox_fpy,\n" +
            "    SUM(fox_delay) AS fox_delay,\n" +
            "    SUM(spi_input) AS spi_input,\n" +
            "    SUM(spi_output) AS spi_output,\n" +
            "    SUM(spi_output)/sum(spi_input)*100 AS spi_fpy,  -- 注意：比率可能需要特殊处理\n" +
            "    SUM(aoi_input) AS aoi_input,\n" +
            "    SUM(aoi_fp_qty) AS aoi_fp_qty,\n" +
            "    SUM(aoi_output) AS aoi_output,\n" +
            "    SUM(aoi_fp_qty)/sum(aoi_input)*100  AS aoi_fpy,  -- 注意：比率可能需要特殊处理\n" +
            "    SUM(aoi_output)/sum(aoi_input)*100 AS aoi_fy,  -- 注意：比率可能需要特殊处理\n" +
            "    SUM(ict_input) AS ict_input,\n" +
            "    SUM(ict_fp_qty) AS ict_fp_qty,\n" +
            "    SUM(ict_output) AS ict_output,\n" +
            "    SUM(ict_fp_qty)/sum(ict_input)*100 AS ict_fpy,  -- 注意：比率可能需要特殊处理\n" +
            "    SUM(ict_output)/sum(ict_input)*100 AS ict_fy,  -- 注意：比率可能需要特殊处理\n" +

            "    SUM(ipqc_latest_close_mil) AS ipqc_latest_close_mil,  -- 注意：比率可能需要特殊处理\n" +
            "    SUM(party_latest_close_mil) AS party_latest_close_mil  -- 注意：比率可能需要特殊处理\n" +
            "FROM \n" +
            "    smtdashboard\n" +
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY \n" +
            "    line\n" +
            "ORDER BY \n" +
            "    CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);" +
            "</script>")
    List<Smtdashboard> queryDashboard(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

    @ResultMap("qualityResultMap")
    @Select("<script>" +
            "SELECT \n" +
            "    month,line,\n" +
            "    SUM(claim_total) AS claim_total,\n" +
            "    SUM(claim_h1) AS claim_h1,\n" +
            "    SUM(claim_c) AS claim_c,\n" +
            "    SUM(sorting_fee_total) AS sorting_fee_total,\n" +
            "    SUM(sorting_fee_iqc) AS sorting_fee_iqc,\n" +
            "    SUM(sorting_fee_claim) AS sorting_fee_claim,\n" +
            "    SUM(input_amount) AS input_amount,\n" +
            "    SUM(scarp_amount) AS scarp_amount,\n" +
            "    AVG(scarp_rate) AS scarp_rate,  -- 注意：比率可能需要特殊处理\n" +
            "    SUM(non_conformity) AS non_conformity,\n" +
            "    SUM(capa) AS capa,\n" +
            "    SUM(oqc_scan_qty) AS oqc_scan_qty,\n" +
            "    SUM(oqc_scan_ng_qty) AS oqc_scan_ng_qty,\n" +
            "    AVG(oqc_scan_ng) AS oqc_scan_ng,  -- 注意：比率可能需要特殊处理\n" +
            "    SUM(oqc_insp_lot) AS oqc_insp_lot,\n" +
            "    SUM(oqc_reject_lot) AS oqc_reject_lot,\n" +
            "    SUM(oqc_reject_lot)/sum(oqc_insp_lot)*100 AS oqc_lrr,  -- 注意：比率可能需要特殊处理\n" +
            "    SUM(oqc_insp_qty) AS oqc_insp_qty,\n" +
            "    SUM(oqc_ng_qty) AS oqc_ng_qty,\n" +
            "    SUM(oqc_dppm) AS oqc_dppm,  -- 注意：比率可能需要特殊处理\n" +
            "    SUM(oba_times) AS oba_times,\n" +
            "    SUM(oba_reject_time) AS oba_reject_time,\n" +
            "    AVG(oba_ng) AS oba_ng,  -- 注意：比率可能需要特殊处理\n" +
            "    SUM(ipqc_findings) AS ipqc_findings,\n" +
            "    SUM(ipqc_major) AS ipqc_major,\n" +
            "    SUM(ipqc_minor) AS ipqc_minor,\n" +
            "    SUM(ipqc_close_mil) AS ipqc_close_mil,\n" +
            "    AVG(ipqc_close_rate) AS ipqc_close_rate,  -- 注意：比率可能需要特殊处理\n" +
            "    SUM(party_findings_3rd) AS party_findings_3rd,\n" +
            "    SUM(party_major_3rd) AS party_major_3rd,\n" +
            "    SUM(party_minor_3rd) AS party_minor_3rd,\n" +
            "    SUM(party_close_mil_3rd) AS party_close_mil_3rd,\n" +
            "    AVG(party_close_rate_3rd) AS party_close_rate_3rd,  -- 注意：比率可能需要特殊处理\n" +
            "    SUM(compliance_total) AS compliance_total,\n" +
            "    SUM(comp_customer) AS comp_customer,\n" +
            "    SUM(comp_internal) AS comp_internal,\n" +
            "    SUM(fox_total) AS fox_total,\n" +
            "    SUM(fox_ict) AS fox_ict,\n" +
            "    SUM(fox_spi) AS fox_spi,\n" +
            "    SUM(fox_fpy) AS fox_fpy,\n" +
            "    SUM(fox_delay) AS fox_delay,\n" +
            "    SUM(spi_input) AS spi_input,\n" +
            "    SUM(spi_output) AS spi_output,\n" +
            "    SUM(spi_output)/sum(spi_input)*100 AS spi_fpy,  -- 注意：比率可能需要特殊处理\n" +
            "    SUM(aoi_input) AS aoi_input,\n" +
            "    SUM(aoi_fp_qty) AS aoi_fp_qty,\n" +
            "    SUM(aoi_output) AS aoi_output,\n" +
            "    SUM(aoi_fp_qty)/sum(aoi_input)*100  AS aoi_fpy,  -- 注意：比率可能需要特殊处理\n" +
            "    SUM(aoi_output)/sum(aoi_input)*100 AS aoi_fy,  -- 注意：比率可能需要特殊处理\n" +
            "    SUM(ict_input) AS ict_input,\n" +
            "    SUM(ict_fp_qty) AS ict_fp_qty,\n" +
            "    SUM(ict_output) AS ict_output,\n" +
            "    SUM(ict_fp_qty)/sum(ict_input)*100 AS ict_fpy,  -- 注意：比率可能需要特殊处理\n" +
            "    SUM(ict_output)/sum(ict_input)*100 AS ict_fy  -- 注意：比率可能需要特殊处理\n" +
            "FROM \n" +
            "    smtdashboard\n" +
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY \n" +
            "    month,line\n" +
            "ORDER BY \n" +
            "    STR_TO_DATE(month, '%Y-%b'),CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);" +
            "</script>")
    List<Smtdashboard> queryDashboardMonthList(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);


    @Select("<script>" +
            "select line,sum(non_conformity) as sum1 from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY line order by CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryNonConformity(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

    @Select("<script>" +
            "select month,line,sum(non_conformity) as sum1 from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY month,line order by STR_TO_DATE(month, '%Y-%b'),CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryNonConformityMonthList(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);


    @Select("<script>" +
            "select line,sum(sorting_fee_total) as sum1,sum(sorting_fee_iqc) as sum2,sum(sorting_fee_claim) as sum3 from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY line order by CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> querySortingFee(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

    @Select("<script>" +
            "select month,line,sum(sorting_fee_total) as sum1,sum(sorting_fee_iqc) as sum2,sum(sorting_fee_claim) as sum3 from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY month,line order by STR_TO_DATE(month, '%Y-%b'),CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> querySortingFeeMonthList(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);


    @Select("<script>" +
            "select line,sum(input_amount) as sum1,sum(scarp_amount) as sum2, sum(scarp_amount)/sum(input_amount) as sum3 from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY line order by CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryScrapRatio(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

    @Select("<script>" +
            "select month,line,sum(input_amount) as sum1,sum(scarp_amount) as sum2, sum(scarp_amount)/sum(input_amount) as sum3 from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY month,line order by STR_TO_DATE(month, '%Y-%b'),CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryScrapRatioMonthList(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);


    @Select("<script>" +
            "select line,sum(capa) as sum1 from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY line order by CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryCAPA(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

    @Select("<script>" +
            "select month,line,sum(capa) as sum1 from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY month,line order by STR_TO_DATE(month, '%Y-%b'),CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryCAPAMonthList(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);


    @Select("<script>" +
            "select line,sum(compliance_total) as sum1,sum(comp_customer) as sum2,sum(comp_internal) as sum3 from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY line order by CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryCompliance(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

    @Select("<script>" +
            "select month,line,sum(compliance_total) as sum1,sum(comp_customer) as sum2,sum(comp_internal) as sum3 from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY month,line order by STR_TO_DATE(month, '%Y-%b'),CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryComplianceMonthList(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

    @Select("<script>" +
            "select line,sum(ipqc_major) as sum1,sum(ipqc_minor) as sum2,sum(ipqc_findings) as sum3,sum(ipqc_close_mil)/(sum(ipqc_major)+sum(ipqc_minor)) as sum4,sum(ipqc_close_mil) sum5 , sum(ipqc_latest_close_mil) as sum6 from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY line order by CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryFindingsIPQC(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

    @Select("<script>" +
            "select month,line,sum(ipqc_major) as sum1,sum(ipqc_minor) as sum2,sum(ipqc_findings) as sum3,sum(ipqc_close_mil)/(sum(ipqc_major)+sum(ipqc_minor)) as sum4,sum(ipqc_close_mil) as sum5 , sum(ipqc_latest_close_mil) as sum6 from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY month,line order by STR_TO_DATE(month, '%Y-%b'),CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryFindingsIPQCMonthList(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

    //修改
    @Select("<script>" +
            "select line,sum(party_major_3rd) as sum1,sum(party_minor_3rd) as sum2,sum(party_findings_3rd) as sum3,sum(party_close_mil_3rd) as sum4,sum(party_latest_close_mil) sum5  from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY line order by CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryPartyFinding3rd(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

    @Select("<script>" +
            "select month,line,sum(party_major_3rd) as sum1,sum(party_minor_3rd) as sum2,sum(party_findings_3rd) as sum3,sum(party_close_mil_3rd) as sum4 ,sum(party_latest_close_mil) sum5 from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY month,line order by STR_TO_DATE(month, '%Y-%b'),CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryPartyFinding3rdMonthList(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

    @Select("<script>" +
            "select line,sum(oqc_scan_ng_qty)/SUM(oqc_scan_qty)*100 as sum1,sum(oqc_scan_ng_qty) as sum2, SUM(oqc_scan_qty)  as sum3 from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY line order by CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryOQCBatchScan(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

    //this one
    @Select("<script>" +
            "select month,line,sum(oqc_scan_ng_qty)/SUM(oqc_scan_qty)*100 as sum1,sum(oqc_scan_ng_qty) as sum2, SUM(oqc_scan_qty)  as sum3  from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY month,line order by STR_TO_DATE(month, '%Y-%b'),CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryOQCBatchScanMonthList(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);


    @Select("<script>" +
            "select line,sum(oqc_insp_lot) as sum1,SUM(oqc_reject_lot) as sum2,SUM(oqc_reject_lot)/sum(oqc_insp_lot)*100 as sum3  from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY line order by CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryOQCLrr(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

    @Select("<script>" +
            "select month,line,sum(oqc_insp_lot) as sum1,SUM(oqc_reject_lot) as sum2,SUM(oqc_reject_lot)/sum(oqc_insp_lot)*100 as sum3  from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY month,line order by STR_TO_DATE(month, '%Y-%b'),CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryOQCLrrMonthList(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

    @Select("<script>" +
            "select line,sum(oqc_ng_qty)/sum(oqc_insp_qty)*1000000 as sum1,sum(oqc_ng_qty) as sum2, sum(oqc_insp_qty) as sum3 from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY line order by CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryOQCDppm(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

    @Select("<script>" +
            "select month,line,sum(oqc_ng_qty)/sum(oqc_insp_qty)*1000000 as sum1,sum(oqc_ng_qty) as sum2, sum(oqc_insp_qty) as sum3  from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY month,line order by STR_TO_DATE(month, '%Y-%b'),CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryOQCDppmMonthList(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

    @Select("<script>" +
            "select line,sum(oba_reject_time) as sum1  from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY line order by CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryOQCOba(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

    @Select("<script>" +
            "select month,line,sum(oba_reject_time) as sum1  from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY month,line order by STR_TO_DATE(month, '%Y-%b'),CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryOQCObaMonthList(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

    @Select("<script>" +
            "select line,sum(fox_delay) as sum1,sum(fox_fpy) as sum2,sum(fox_spi) as sum3,sum(fox_ict) as sum4,sum(fox_total) as sum5  from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY line order by CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryFoxInsight(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

    @Select("<script>" +
            "select month,line,sum(fox_delay) as sum1,sum(fox_fpy) as sum2,sum(fox_spi) as sum3,sum(fox_ict) as sum4,sum(fox_total) as sum5  from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY month,line order by STR_TO_DATE(month, '%Y-%b'),CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryFoxInsightMonthList(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

    @Select("<script>" +
            "select line,SUM(spi_output)/SUM(spi_input)*100 as sum1,SUM(spi_output) as sum2,sum(spi_input) as sum3  from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY line order by CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> querySPIFpy(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

    @Select("<script>" +
            "select month,line,SUM(spi_output)/sum(spi_input)*100 as sum1,SUM(spi_output) as sum2,sum(spi_input) as sum3  from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY month,line order by STR_TO_DATE(month, '%Y-%b'),CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> querySPIFpyMonthList(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

    @Select("<script>" +
            "select line,SUM(aoi_fp_qty)/sum(aoi_input)*100 as sum1,SUM(aoi_output)/sum(aoi_input)*100 as sum2 ,SUM(aoi_fp_qty) as sum3,SUM(aoi_output) as sum4, sum(aoi_input) as sum5 from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY line order by CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryAOIFpy(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

    @Select("<script>" +
            "select month,line,SUM(aoi_fp_qty)/sum(aoi_input)*100 as sum1,SUM(aoi_output)/sum(aoi_input)*100 as sum2,SUM(aoi_fp_qty) as sum3,SUM(aoi_output) as sum4, sum(aoi_input) as sum5  from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY month,line order by STR_TO_DATE(month, '%Y-%b'),CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryAOIFpyMonthList(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

    @Select("<script>" +
            "select line,SUM(ict_fp_qty)/sum(ict_input)*100 as sum1,SUM(ict_output)/sum(ict_input)*100 as sum2,SUM(ict_fp_qty) as sum3,SUM(ict_output) as sum4 ,sum(ict_input) as sum5  from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY line order by CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryICTFpy(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

    @Select("<script>" +
            "select month,line,SUM(ict_fp_qty)/sum(ict_input)*100 as sum1,SUM(ict_output)/sum(ict_input)*100 as sum2 , SUM(ict_fp_qty) as sum3,SUM(ict_output) as sum4 ,sum(ict_input) as sum5 from smtdashboard"+
            "  where 1=1"+
            "  and year=#{year}"+
            "<if test='month.size()>0 and month !=null '>  and month in" +
            "    <foreach item='item' collection='month' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "<if test='line.size()>0 and line !=null '>  and line in" +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>"+
            "GROUP BY month,line order by STR_TO_DATE(month, '%Y-%b'),CAST(SUBSTRING(line, LOCATE('#', line) + 1) AS UNSIGNED);"+
            "</script>")
    List<Charts> queryICTFpyMonthList(@Param("year")String year, @Param("month")List<String> month, @Param("line")List<String> list);

}
